/****** Object:  StoredProcedure [dbo].[AuthZ_spGetGroupsByRoles]    Script Date: 06/24/2009 23:42:20 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_spGetGroupsByRoles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthZ_spGetGroupsByRoles]
GO

/****** Object:  StoredProcedure [dbo].[AuthZ_spGetGroupsByRoles]    Script Date: 06/24/2009 23:42:20 ******/
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		<revans>
-- Create date: <5-12-2009>
-- Description:	<Retrieves all Roles requested and their associated Groups.>
-- Param:
-- =============================================
CREATE PROCEDURE [dbo].[AuthZ_spGetGroupsByRoles]

	@role as nvarchar(max) = ''

AS
BEGIN
	BEGIN TRY
		SET NOCOUNT ON;
		
		IF @role = '' or @role is null
		BEGIN
		--Get all roles and groups.
			SELECT DISTINCT
			r.RoleName
			, ag.GroupName
			FROM Role r
			JOIN RoleMapping rm
			ON   rm.RoleID = r.RoleID
			JOIN AuthorizationGroup ag
			ON   ag.groupid = rm.GroupID
		END
		ELSE
		BEGIN
		--Get just the roles and groups based on the given role(s).
			SELECT DISTINCT
			r.RoleName
			, ag.GroupName
			FROM Role r
			JOIN RoleMapping rm
			ON   rm.RoleID = r.RoleID
			JOIN AuthorizationGroup ag
			ON   ag.groupid = rm.GroupID
			JOIN ISRM_ftStringToTable(@role, ',') pn
			ON   r.RoleName = pn.SplitValue
		END

	END TRY
	
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT @ErrorMessage = ERROR_MESSAGE(),
			   @ErrorSeverity = ERROR_SEVERITY(),
			   @ErrorState = ERROR_STATE();
	           
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   );
	END CATCH
END


GO


